﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_YAB13(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_YAB13(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_YAB13(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_YAB13(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_YAB13(dwData, 1);
            }
            UpdateICC_YAB13(dwLocal);

        }
        public void InsertICC_YAB13(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO ICC_YAB13 (GABMANDANT,GABLO,GABBUCHDAT,GABART,GABVAR,YAB13)"
                        + "SELECT X.GABMANDANT,X.GABLO,X.GABBUCHDAT,B.GABART,MAX(B.GABVAR) AS GABVAR ,SUM(B.GABKG) AS YAB13 "
                        +"    FROM ICC_MGABEW B,    ( SELECT DISTINCT W.GABMANDANT,W.GABLO,W.GABBUCHDAT,P.ABPAUFKRS,P.ABPAUFLFD,P.ABPAUFPOS"
                        +"     FROM ICC_MGABEW W LEFT JOIN ABPOS P   ON  W.GABMANDANT=ABPMANDANT AND W.GABAUFKRS=ABPAUFKRS  AND W.GABAUFLFD=ABPAUFLFD AND  W.GABAUFPOS=ABPAUFPOS"
                        + "     WHERE W.GABAUFTYP='V' AND W.GABAUFKRS NOT LIKE 'D%'     AND GABMANDANT=:MANDANT AND RTRIM(GABBUCHDAT)=:MONTH AND W.GABBS IN('38','58')     ) X"
                        +"    WHERE     B.GABMANDANT=X.GABMANDANT AND B.GABBUCHDAT=X.GABBUCHDAT    AND B.GABAUFKRS=X.ABPAUFKRS  AND B.GABAUFLFD=X.ABPAUFLFD AND  B.GABAUFPOS=X.ABPAUFPOS"
                        +"    AND B.GABBS='13'GROUP BY  X.GABMANDANT,X.GABLO,X.GABBUCHDAT,B.GABART";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public void DeleteICC_YAB13(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_YAB13 X WHERE X.MANDANT=:MANDANT";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_YAB13 X WHERE X.GABMANDANT=:MANDANT AND RTRIM(X.GABBUCHDAT)=:MONTH";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
        public DataSet QueryICC_YAB13(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_YAB13 WHERE MANDANT=:MANDANT  AND MONTH=:MONTH"; ;
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break; 
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_YAB13";
            Return = Result;
            return Return;
        }
        public void UpdateICC_YAB13(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
 
                    break;
                case 2:
                             break;
                case 3:
                        break;
            }
            Connect2Proxy(or1);
        }
    }
}
